﻿using System; 
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Dto;

namespace Matrix.MaKaYunShang.DAL
{
	public partial class CommodityGiftDAL : BaseDAL<CommodityGiftDAL>
	{
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(CommodityGift model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into CommodityGift(");
            strSql.Append("GiftCommodityLibId,BelongToCommodityLibId,GiftQuantity,LeftQuantity,ConsumerGetTotalTimes,TimeSpanCount,TimeSpanType,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@GiftCommodityLibId,@BelongToCommodityLibId,@GiftQuantity,@LeftQuantity,@ConsumerGetTotalTimes,@TimeSpanCount,@TimeSpanType,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@GiftCommodityLibId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BelongToCommodityLibId", SqlDbType.Int,4) ,            
                        new SqlParameter("@GiftQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@LeftQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@ConsumerGetTotalTimes", SqlDbType.Int,4) ,            
                        new SqlParameter("@TimeSpanCount", SqlDbType.Int,4) ,            
                        new SqlParameter("@TimeSpanType", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.GiftCommodityLibId;
            parameters[1].Value = model.BelongToCommodityLibId;
            parameters[2].Value = model.GiftQuantity;
            parameters[3].Value = model.LeftQuantity;
            parameters[4].Value = model.ConsumerGetTotalTimes;
            parameters[5].Value = model.TimeSpanCount;
            parameters[6].Value = model.TimeSpanType;
            parameters[7].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(CommodityGift model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CommodityGift set ");

            strSql.Append(" GiftCommodityLibId = @GiftCommodityLibId , ");
            strSql.Append(" BelongToCommodityLibId = @BelongToCommodityLibId , ");
            strSql.Append(" GiftQuantity = @GiftQuantity , ");
            strSql.Append(" LeftQuantity = @LeftQuantity , ");
            strSql.Append(" ConsumerGetTotalTimes = @ConsumerGetTotalTimes , ");
            strSql.Append(" TimeSpanCount = @TimeSpanCount , ");
            strSql.Append(" TimeSpanType = @TimeSpanType , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@GiftCommodityLibId", SqlDbType.Int,4) ,            
                        new SqlParameter("@BelongToCommodityLibId", SqlDbType.Int,4) ,            
                        new SqlParameter("@GiftQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@LeftQuantity", SqlDbType.Int,4) ,            
                        new SqlParameter("@ConsumerGetTotalTimes", SqlDbType.Int,4) ,            
                        new SqlParameter("@TimeSpanCount", SqlDbType.Int,4) ,            
                        new SqlParameter("@TimeSpanType", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.GiftCommodityLibId;
            parameters[2].Value = model.BelongToCommodityLibId;
            parameters[3].Value = model.GiftQuantity;
            parameters[4].Value = model.LeftQuantity;
            parameters[5].Value = model.ConsumerGetTotalTimes;
            parameters[6].Value = model.TimeSpanCount;
            parameters[7].Value = model.TimeSpanType;
            parameters[8].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CommodityGift set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CommodityGift set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, GiftCommodityLibId, BelongToCommodityLibId, GiftQuantity, LeftQuantity, ConsumerGetTotalTimes, TimeSpanCount, TimeSpanType, IsDelete  ");
            strSql.Append("  from CommodityGift ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM CommodityGift where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion
		
		#region 扩展方法
        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="searchKeyWord"></param>
        /// <param name="colOrder"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<ViewCommodityGiftDto> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, ref int totalCount)
        {
            string strWhere = string.Empty;

            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere += " AND (GiftCommodityName LIKE '%" + searchKeyWord + "%' OR BelongToCommodityName LIKE '%" + searchKeyWord +
                            "%' OR GiftQuantity LIKE '%" + searchKeyWord + "%' OR LeftQuantity LIKE '%" + searchKeyWord + "%') ";
            }

            const string tables = "( SELECT cg.Id, cg.GiftCommodityLibId, cl.CommodityName AS GiftCommodityName, " +
                                  "cg.BelongToCommodityLibId, cl2.CommodityName AS BelongToCommodityName, " +
                                  "cg.GiftQuantity, cg.LeftQuantity " +
                                  "FROM CommodityGift cg " +
                                  "INNER JOIN CommodityLib cl ON cg.GiftCommodityLibId = cl.Id " +
                                  "INNER JOIN CommodityLib cl2 ON cg.BelongToCommodityLibId = cl2.Id " +
                                  "WHERE cg.IsDelete = 0 AND cl.IsDelete = 0 AND cl2.IsDelete = 0 ) n ";

            return GetSimpleListPage<ViewCommodityGiftDto>(pageIndex, pageSize, tables, null, null, colOrder, strWhere, ref totalCount);
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool BatchDeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update CommodityGift set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ");  ");

            var arList = Idlist.Split(',');
            if (arList.Length > 0)
            {
                strSql.Append("Update CommodityLib SET GiftCount=(CASE WHEN  (GiftCount-" + arList.Length + ")<=0 THEN 0 ELSE  GiftCount-" + arList.Length + " END), ");
                strSql.Append("[HasGiftCommodity]=(CASE WHEN (GiftCount-" + arList.Length + ")<=0 THEN 0 ELSE 1 END) ");
                strSql.Append("WHERE Id IN (SELECT BelongToCommodityLibId FROM CommodityGift WHERE Id in (" + Idlist +
                              "));");
            }
            
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 通过赠品的商品ID和所属的商品ID获取对象
        /// </summary>
        /// <param name="giftCommodityId"></param>
        /// <param name="belongToCommodityLibId"></param>
        /// <returns></returns>
        public CommodityGift GetModelByGiftCmdtyIdAndBelongCmdtyId(int giftCommodityId, int belongToCommodityLibId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, GiftCommodityLibId, BelongToCommodityLibId, GiftQuantity, LeftQuantity, ConsumerGetTotalTimes, TimeSpanCount, TimeSpanType, IsDelete  ");
            strSql.Append("  from CommodityGift ");
            strSql.Append(" where GiftCommodityLibId=@GiftCommodityLibId AND BelongToCommodityLibId=@BelongToCommodityLibId AND IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@GiftCommodityLibId", SqlDbType.Int,4),
                    new SqlParameter("@BelongToCommodityLibId", SqlDbType.Int,4)
			};
            parameters[0].Value = giftCommodityId;
            parameters[1].Value = belongToCommodityLibId;

            return DbHelperSQL.ExecuteReader<CommodityGift>(strSql.ToString(), parameters);
        }
		#endregion
	}
}
